package cn.iocoder.yudao.module.ao.service.vocationalclassschedule;

import cn.hutool.core.util.StrUtil;
import cn.iocoder.yudao.module.ao.controller.admin.vocationalclassschedule.vo.VocationalClassScheduleImportExcelVO;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.springframework.stereotype.Component;

import java.io.InputStream;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * 中高技班级课Excel解析器
 * 用于解析复杂的课程表格式
 */
@Slf4j
@Component
public class VocationalClassScheduleExcelParser {

    // 学期信息的正则表达式
    private static final Pattern TERM_PATTERN = Pattern.compile("(\\d{4}-\\d{4}学年\\s*第[一二]学期)");
    
    // 班级名称的正则表达式
    private static final Pattern CLASS_NAME_PATTERN = Pattern.compile("([^\\s]+班)");

    // 班主任的正则表达式
    private static final Pattern CLASS_TEACHER_PATTERN = Pattern.compile("班主任[：:]?\\s*([\\u4e00-\\u9fa5]{2,4})");

    // 教室的正则表达式 - 匹配如D216、B215等格式
    private static final Pattern CLASSROOM_PATTERN = Pattern.compile("([A-Z]\\d{3,4})");

    /**
     * 解析Excel文件
     */
    public List<VocationalClassScheduleImportExcelVO> parseExcel(InputStream inputStream, String sheetName) throws Exception {
        log.info("开始解析中高技班级课Excel文件，工作表名称: {}", sheetName);
        List<VocationalClassScheduleImportExcelVO> result = new ArrayList<>();

        try (Workbook workbook = WorkbookFactory.create(inputStream)) {
            log.info("成功创建工作簿，工作表数量: {}", workbook.getNumberOfSheets());

            Sheet sheet;
            if (StrUtil.isNotBlank(sheetName)) {
                sheet = workbook.getSheet(sheetName);
                log.info("根据名称获取工作表: {}, 结果: {}", sheetName, sheet != null ? "成功" : "失败");
            } else {
                sheet = workbook.getSheetAt(0);
                log.info("获取第一个工作表: {}", sheet != null ? sheet.getSheetName() : "null");
            }

            if (sheet == null) {
                log.warn("工作表为空，返回空结果");
                return result;
            }

            log.info("开始解析工作表: {}, 行数: {}", sheet.getSheetName(), sheet.getLastRowNum() + 1);

            // 解析表格
            List<TableInfo> tables = parseTablesFromSheet(sheet);
            log.info("解析到 {} 个表格", tables.size());

            // 将每个表格转换为导入VO
            for (TableInfo table : tables) {
                List<VocationalClassScheduleImportExcelVO> tableData = convertTableToVOs(table);
                log.info("表格转换完成，生成 {} 条数据", tableData.size());
                result.addAll(tableData);
            }
        }

        log.info("Excel解析完成，总共生成 {} 条数据", result.size());
        return result;
    }

    /**
     * 从工作表中解析所有表格
     */
    private List<TableInfo> parseTablesFromSheet(Sheet sheet) {
        List<TableInfo> tables = new ArrayList<>();
        String currentClassName = "";

        for (int rowIndex = 0; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
            Row row = sheet.getRow(rowIndex);
            if (row == null) continue;

            // 检查是否包含班级名称信息
            String rowText = getRowText(row);
            if (containsClassKeyword(rowText)) {
                String extractedClassName = extractClassNameFromTitle(rowText);
                if (StrUtil.isNotBlank(extractedClassName)) {
                    currentClassName = extractedClassName;
                    log.info("在第{}行发现班级名称: {}", rowIndex + 1, currentClassName);
                }
            }

            // 检查是否是表格标题行
            String firstCellValue = getCellStringValue(row.getCell(0));
            if (StrUtil.isNotBlank(firstCellValue)) {
                Matcher termMatcher = TERM_PATTERN.matcher(firstCellValue);
                if (termMatcher.find()) {
                    // 找到学期信息，开始解析表格
                    TableInfo table = parseTable(sheet, rowIndex);
                    if (table != null) {
                        // 如果表格中没有班级名称，使用当前记录的班级名称
                        if (StrUtil.isBlank(table.className) && StrUtil.isNotBlank(currentClassName)) {
                            table.className = currentClassName;
                            log.info("为表格分配班级名称: {}", currentClassName);
                        }
                        tables.add(table);
                        rowIndex = table.endRow; // 跳过已解析的行
                    }
                }
            }
        }

        return tables;
    }

    /**
     * 解析单个表格
     */
    private TableInfo parseTable(Sheet sheet, int startRow) {
        Row titleRow = sheet.getRow(startRow);
        if (titleRow == null) return null;

        String titleText = getCellStringValue(titleRow.getCell(0));
        log.info("解析表格标题: {}", titleText);

        // 提取学期信息
        Matcher termMatcher = TERM_PATTERN.matcher(titleText);
        String term = termMatcher.find() ? termMatcher.group(1) : "";

        // 提取班级名称
        String className = extractClassNameFromTitle(titleText);

        // 提取班主任和教室信息
        String classTeacher = extractClassTeacherFromTitle(titleText);
        String classroom = extractClassroomFromTitle(titleText);

        // 查找表头行（包含"节次"、"周一"等）
        int headerRow = findHeaderRow(sheet, startRow);
        if (headerRow == -1) {
            log.warn("未找到表头行");
            return null;
        }

        // 解析表头
        Map<Integer, String> columnMapping = parseHeader(sheet.getRow(headerRow));
        log.info("表头解析完成，列映射: {}", columnMapping);

        // 解析数据行
        List<Map<String, String>> dataRows = new ArrayList<>();
        int currentRow = headerRow + 1;
        
        while (currentRow <= sheet.getLastRowNum()) {
            Row row = sheet.getRow(currentRow);
            if (row == null || isEmptyRow(row)) {
                currentRow++;
                continue;
            }

            // 检查是否到达下一个表格
            String firstCell = getCellStringValue(row.getCell(0));
            if (StrUtil.isNotBlank(firstCell) && TERM_PATTERN.matcher(firstCell).find()) {
                break;
            }

            Map<String, String> rowData = parseDataRow(row, columnMapping);
            if (!rowData.isEmpty()) {
                dataRows.add(rowData);
            }
            currentRow++;
        }

        TableInfo table = new TableInfo();
        table.term = term;
        table.className = className;
        table.classTeacher = classTeacher;
        table.classroom = classroom;
        table.startRow = startRow;
        table.endRow = currentRow - 1;
        table.dataRows = dataRows;

        log.info("表格解析完成: 学期={}, 班级={}, 数据行数={}", term, className, dataRows.size());
        return table;
    }

    /**
     * 查找表头行
     */
    private int findHeaderRow(Sheet sheet, int startRow) {
        for (int i = startRow; i <= Math.min(startRow + 5, sheet.getLastRowNum()); i++) {
            Row row = sheet.getRow(i);
            if (row == null) continue;

            String firstCell = getCellStringValue(row.getCell(0));
            if ("节次".equals(firstCell) || firstCell.contains("节次")) {
                return i;
            }
        }
        return -1;
    }

    /**
     * 解析表头
     */
    private Map<Integer, String> parseHeader(Row headerRow) {
        Map<Integer, String> columnMapping = new HashMap<>();
        
        for (int i = 0; i < headerRow.getLastCellNum(); i++) {
            String cellValue = getCellStringValue(headerRow.getCell(i));
            if (StrUtil.isNotBlank(cellValue)) {
                cellValue = cellValue.trim();
                if ("节次".equals(cellValue)) {
                    columnMapping.put(i, "classSection");
                } else if ("周一".equals(cellValue) || cellValue.contains("周一")) {
                    columnMapping.put(i, "mondayCourse");
                } else if ("周二".equals(cellValue) || cellValue.contains("周二")) {
                    columnMapping.put(i, "tuesdayCourse");
                } else if ("周三".equals(cellValue) || cellValue.contains("周三")) {
                    columnMapping.put(i, "wednesdayCourse");
                } else if ("周四".equals(cellValue) || cellValue.contains("周四")) {
                    columnMapping.put(i, "thursdayCourse");
                } else if ("周五".equals(cellValue) || cellValue.contains("周五")) {
                    columnMapping.put(i, "fridayCourse");
                } else if ("周六".equals(cellValue) || cellValue.contains("周六")) {
                    columnMapping.put(i, "saturdayCourse");
                } else if ("周日".equals(cellValue) || cellValue.contains("周日") || "周天".equals(cellValue) || cellValue.contains("周天")) {
                    columnMapping.put(i, "sundayCourse");
                }
            }
        }
        
        return columnMapping;
    }

    /**
     * 解析数据行
     */
    private Map<String, String> parseDataRow(Row row, Map<Integer, String> columnMapping) {
        Map<String, String> rowData = new HashMap<>();
        
        for (Map.Entry<Integer, String> entry : columnMapping.entrySet()) {
            int colIndex = entry.getKey();
            String fieldName = entry.getValue();
            String cellValue = getCellStringValue(row.getCell(colIndex));
            
            if (StrUtil.isNotBlank(cellValue)) {
                rowData.put(fieldName, cellValue.trim());
            }
        }
        
        return rowData;
    }

    /**
     * 将表格转换为VO对象
     */
    private List<VocationalClassScheduleImportExcelVO> convertTableToVOs(TableInfo table) {
        List<VocationalClassScheduleImportExcelVO> result = new ArrayList<>();
        
        for (Map<String, String> rowData : table.dataRows) {
            VocationalClassScheduleImportExcelVO vo = VocationalClassScheduleImportExcelVO.builder()
                .term(table.term)
                .className(table.className)
                .classSection(rowData.get("classSection"))
                .mondayCourse(rowData.get("mondayCourse"))
                .tuesdayCourse(rowData.get("tuesdayCourse"))
                .wednesdayCourse(rowData.get("wednesdayCourse"))
                .thursdayCourse(rowData.get("thursdayCourse"))
                .fridayCourse(rowData.get("fridayCourse"))
                .saturdayCourse(rowData.get("saturdayCourse"))
                .sundayCourse(rowData.get("sundayCourse"))
                .classTeacher(table.classTeacher)
                .classroom(table.classroom)
                .build();
            
            result.add(vo);
        }
        
        return result;
    }

    /**
     * 检查是否包含班级关键词
     */
    private boolean containsClassKeyword(String text) {
        if (StrUtil.isBlank(text)) return false;
        return text.contains("班") && (text.contains("体验") || text.contains("春") || text.contains("秋") || 
               text.matches(".*\\d+.*班.*"));
    }

    /**
     * 从标题中提取班级名称
     */
    private String extractClassNameFromTitle(String title) {
        if (StrUtil.isBlank(title)) return "";

        Matcher matcher = CLASS_NAME_PATTERN.matcher(title);
        if (matcher.find()) {
            return matcher.group(1);
        }

        return "";
    }

    /**
     * 从标题中提取班主任姓名
     */
    private String extractClassTeacherFromTitle(String title) {
        if (StrUtil.isBlank(title)) return "";

        // 先尝试匹配"班主任：XXX"格式
        Matcher matcher = CLASS_TEACHER_PATTERN.matcher(title);
        if (matcher.find()) {
            return matcher.group(1);
        }

        // 如果没有找到，尝试查找常见的中文姓名（2-4个汉字）
        // 排除常见的非姓名词汇
        String[] excludeWords = {"学年", "学期", "班级", "课表", "周一", "周二", "周三", "周四", "周五",
                                "节次", "早上", "上午", "下午", "晚上", "机房", "实训", "教室", "高技"};

        // 使用正则表达式查找2-4个连续汉字的姓名
        Pattern namePattern = Pattern.compile("[\\u4e00-\\u9fa5]{2,4}");
        Matcher nameMatcher = namePattern.matcher(title);

        // 收集所有可能的姓名候选
        List<String> candidates = new ArrayList<>();
        while (nameMatcher.find()) {
            String candidate = nameMatcher.group();
            boolean isExcluded = false;

            // 检查是否为排除词汇
            for (String exclude : excludeWords) {
                if (candidate.contains(exclude) || exclude.contains(candidate)) {
                    isExcluded = true;
                    break;
                }
            }

            if (!isExcluded) {
                candidates.add(candidate);
            }
        }

        // 如果有多个候选，选择最后一个（通常班主任姓名在标题末尾）
        if (!candidates.isEmpty()) {
            return candidates.get(candidates.size() - 1);
        }

        return "";
    }

    /**
     * 从标题中提取教室信息
     */
    private String extractClassroomFromTitle(String title) {
        if (StrUtil.isBlank(title)) return "";

        // 先尝试匹配标准教室格式：字母+数字（如D216、B215）
        Matcher matcher = CLASSROOM_PATTERN.matcher(title);
        if (matcher.find()) {
            return matcher.group(1);
        }

        // 如果没找到，尝试查找"教室："后面的内容
        Pattern classroomLabelPattern = Pattern.compile("教室[：:]?\\s*([A-Z]?\\d{3,4})");
        Matcher labelMatcher = classroomLabelPattern.matcher(title);
        if (labelMatcher.find()) {
            return labelMatcher.group(1);
        }

        return "";
    }

    /**
     * 获取行的所有文本
     */
    private String getRowText(Row row) {
        StringBuilder sb = new StringBuilder();
        for (int i = 0; i < row.getLastCellNum(); i++) {
            Cell cell = row.getCell(i);
            if (cell != null) {
                String cellValue = getCellStringValue(cell);
                if (StrUtil.isNotBlank(cellValue)) {
                    sb.append(cellValue).append(" ");
                }
            }
        }
        return sb.toString().trim();
    }

    /**
     * 获取单元格字符串值
     */
    private String getCellStringValue(Cell cell) {
        if (cell == null) return "";
        
        try {
            switch (cell.getCellType()) {
                case STRING:
                    return cell.getStringCellValue();
                case NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell)) {
                        return cell.getDateCellValue().toString();
                    } else {
                        return String.valueOf((long) cell.getNumericCellValue());
                    }
                case BOOLEAN:
                    return String.valueOf(cell.getBooleanCellValue());
                case FORMULA:
                    return cell.getCellFormula();
                default:
                    return "";
            }
        } catch (Exception e) {
            log.warn("读取单元格值失败: {}", e.getMessage());
            return "";
        }
    }

    /**
     * 检查是否为空行
     */
    private boolean isEmptyRow(Row row) {
        for (int i = 0; i < row.getLastCellNum(); i++) {
            Cell cell = row.getCell(i);
            if (cell != null && StrUtil.isNotBlank(getCellStringValue(cell))) {
                return false;
            }
        }
        return true;
    }

    /**
     * 表格信息类
     */
    private static class TableInfo {
        String term;
        String className;
        String classTeacher;
        String classroom;
        int startRow;
        int endRow;
        List<Map<String, String>> dataRows;
    }
}
